﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using MySql.Data.MySqlClient;
using DateBaseConnectionPool;

namespace TestMySqlConnetPoll
{
    public struct objTianChuang
    {
        public byte type;
        public int s_time;
        public int e_time;
        public string WorkText;
        public string UserName;
        public int fact_stime;
        public int fact_etime;
    }
    public class TianChuangXiuTable
    {
        #region 变量定义，天窗修
        
        public List<objTianChuang> ValueList = new List<objTianChuang>();
        public bool ReadyValueList = false;
        public int ErrorFlag = 0;

        struct threadOBJ
        {
           public objDate stime;
           public objDate etime;
        }

        public void ReadValueList(objDate sdate, objDate edate)
        {
            ErrorFlag = 0;
            ValueList.Clear();
            ReadyValueList = false;
            threadOBJ obj = new threadOBJ();
            obj.stime = sdate;
            obj.etime = edate;
            ThreadPool.QueueUserWorkItem(new WaitCallback(ThreadReadValueList), obj);
        }
        #endregion
        private Mutex keyMutex = new Mutex();
        static private int keykey = 0;
        #region 天窗修
        public bool InsertTable(objDate date, objTianChuang obj)
        {
            bool tmpFlag = false;
            string TableName = "TianChuangXiu";
            if (MySql.c != null && MySql.c.State == PoolState.Run)
            {
                MySqlConnection con = new MySqlConnection();
                string key = TableName + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Interlocked.Increment(ref keykey).ToString();
                try
                {
                    con = (MySqlConnection)MySql.c.GetConnectionFormPool(key);
                }
                catch (MySqlException ex)
                {
                    //写异常日志
                    ErrorFlag = 1;
                    tmpFlag = false;
                }
                
                if (con != null)
                {
                    StringBuilder ds = new StringBuilder();
                    ds.Append(string.Format("delete from {0} where year = {1} and month = {2} and day = {3} and type = {4} and s_time = {5}",
                                             TableName, date.year, date.month, date.day, obj.type, obj.s_time));
                    MySqlCommand dc = new MySqlCommand(ds.ToString(), con);
                    try
                    {
                        dc.ExecuteNonQuery();       //如果是更新，需要先删除记录
                    }
                    catch (MySqlException ex)
                    {
                        //写异常日志
                        ErrorFlag = 2;
                    }

                    StringBuilder InsertStatement = new StringBuilder();
                    InsertStatement.Append(string.Format("insert into {0}(year,month,day,type,s_time,e_time,WorkText,UserName,fact_stime,fact_etime) ", TableName));
                    InsertStatement.Append(" values(?year,?month,?day,?type,?s_time,?e_time,?WorkText,?UserName,?fact_stime,?fact_etime)");
                    MySqlCommand InsertCommand = new MySqlCommand(InsertStatement.ToString(), con);
                    InsertCommand.Parameters.Add(new MySqlParameter("?year", MySqlDbType.Int32)).Value = date.year;
                    InsertCommand.Parameters.Add(new MySqlParameter("?month", MySqlDbType.Byte)).Value = date.month;
                    InsertCommand.Parameters.Add(new MySqlParameter("?day", MySqlDbType.Byte)).Value = date.day;
                    InsertCommand.Parameters.Add(new MySqlParameter("?type", MySqlDbType.Byte)).Value = obj.type;
                    InsertCommand.Parameters.Add(new MySqlParameter("?s_time", MySqlDbType.Int32)).Value = obj.s_time;
                    InsertCommand.Parameters.Add(new MySqlParameter("?e_time", MySqlDbType.Int32)).Value = obj.e_time;
                    InsertCommand.Parameters.Add(new MySqlParameter("?WorkText", MySqlDbType.VarChar, 1024)).Value = obj.WorkText;
                    InsertCommand.Parameters.Add(new MySqlParameter("?UserName", MySqlDbType.VarChar, 20)).Value = obj.UserName;
                    InsertCommand.Parameters.Add(new MySqlParameter("?fact_stime", MySqlDbType.Int32)).Value = obj.fact_stime;
                    InsertCommand.Parameters.Add(new MySqlParameter("?fact_etime", MySqlDbType.Int32)).Value = obj.fact_etime;

                    try
                    {
                        int k = InsertCommand.ExecuteNonQuery();
                        if (k == 1) tmpFlag = true;        //写入成功
                        else
                        {
                            ErrorFlag = 3;
                            tmpFlag = false;              //写入失败
                        }
                    }
                    catch (MySqlException e)
                    {
                        //写异常日记
                        ErrorFlag = 4;
                        tmpFlag = false;       //写入失败
                    }
                    try
                    {
                        MySql.c.DisposeConnection(key);
                    }
                    catch (MySqlException e)
                    {
                        //写异常日记
                        ErrorFlag = 5;
                        tmpFlag = false;
                    }
                }
            }
            return tmpFlag;
        }

        public bool delteTianChuangValue(objDate date, objTianChuang obj)
        {
            bool tmpFlag = false;
            string TableName = "TianChuangXiu";
            if (MySql.c != null && MySql.c.State == PoolState.Run)
            {
                MySqlConnection con = new MySqlConnection();
                string key = TableName + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Interlocked.Increment(ref keykey).ToString();
                try
                {
                    con = (MySqlConnection)MySql.c.GetConnectionFormPool(key);
                }
                catch (MySqlException ex)
                {
                    //写异常日志
                    ErrorFlag = 1;
                    tmpFlag = false;
                }
                
                if (con != null)
                {
                    StringBuilder ds = new StringBuilder();
                    ds.Append(string.Format("delete from {0} where year = {1} and month = {2} and day = {3} and type = {4} and s_time = {5}",
                                             TableName, date.year, date.month, date.day, obj.type, obj.s_time));
                    MySqlCommand dc = new MySqlCommand(ds.ToString(), con);
                    try
                    {
                        dc.ExecuteNonQuery();       //如果是更新，需要先删除记录
                    }
                    catch (MySqlException ex)
                    {
                        //写异常日志
                        ErrorFlag = 2;
                    }
                    try
                    {
                        MySql.c.DisposeConnection(key);
                    }
                    catch (MySqlException e)
                    {
                        //写异常日记
                        ErrorFlag = 5;
                        tmpFlag = false;
                    }
                }
            }
            return tmpFlag;
        }

        private void ThreadReadValueList(object o)
        {
            threadOBJ obj = (threadOBJ)o;
            objDate sdate = obj.stime;
            objDate edate = obj.etime;
            string TableName = "TianChuangXiu";
            ValueList.Clear();
            if (MySql.c != null && MySql.c.State == PoolState.Run)
            {
                MySqlConnection con = new MySqlConnection();
                string key = TableName + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Interlocked.Increment(ref keykey).ToString();
                try
                {
                    con = (MySqlConnection)MySql.c.GetConnectionFormPool(key);
                }
                catch (MySqlException ex)
                {
                    //写异常日志
                    ErrorFlag = 1;
                }
                if (con != null)
                {
                    StringBuilder SelectStatement = new StringBuilder();

                    SelectStatement.Append(string.Format("select type,s_time,e_time,WorkText,UserName,fact_stime,fact_etime from {0} ", TableName));
                    SelectStatement.Append(string.Format(" where year >= {0} and year <= {1} and month >= {2} and month<={3} and day >= {4} and day<={5}",
                                                         sdate.year, edate.year, sdate.month, edate.month, sdate.day, edate.day));

                    try
                    {
                        MySqlCommand selectCommand = new MySqlCommand(SelectStatement.ToString(), con);
                        selectCommand.CommandTimeout = 120;   //120s操作超时

                        MySqlDataReader read = selectCommand.ExecuteReader();
                        while (read.Read())
                        {
                            objTianChuang t = new objTianChuang();
                            t.type = read.GetByte(read.GetOrdinal("type"));
                            t.s_time = read.GetInt32(read.GetOrdinal("s_time"));
                            t.e_time = read.GetInt32(read.GetOrdinal("e_time"));
                            if (!read.IsDBNull(read.GetOrdinal("WorkText")))
                            {
                                t.WorkText = read.GetString(read.GetOrdinal("WorkText"));
                            }
                            if (!read.IsDBNull(read.GetOrdinal("UserName")))
                            {
                                t.UserName = read.GetString(read.GetOrdinal("UserName"));
                            }
                            t.fact_stime = read.GetInt32(read.GetOrdinal("fact_stime"));
                            t.fact_etime = read.GetInt32(read.GetOrdinal("fact_etime"));
                            ValueList.Add(t);
                        }
                        read.Close();
                        ReadyValueList = true;
                    }
                    catch (MySqlException e)
                    {
                        //写错误日志
                        ErrorFlag = 2;
                    }
                    try
                    {
                        MySql.c.DisposeConnection(key);
                    }
                    catch (MySqlException e)
                    {
                        //写错误日志
                        ErrorFlag = 3;
                    }
                }
                else ErrorFlag = 6;
            }
            else ErrorFlag = 7;
        }
        #endregion
    }
}
